package com.jiang.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import com.jiang.dao.INewsDAO;
import com.jiang.vo.News;

public class NewsDAOImpl implements INewsDAO {

	private Connection conn = null;

	public NewsDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public Boolean doCreateNews(News news) throws SQLException {

		String sql = " INSERT INTO news(title,pubdate,content) VALUES(?,?,?) ";
		PreparedStatement ps = this.conn.prepareStatement(sql);
		ps.setString(1, news.getTitle());
		ps.setDate(2, new java.sql.Date(news.getPubdate().getTime()));
		ps.setString(3, news.getContent());
		return ps.executeUpdate() > 0;

	}

	@Override
	public Boolean doRemoveBatch(Set<Integer> nids) throws SQLException {

		StringBuffer sb = new StringBuffer();
		sb.append(" DELETE FROM news WHERE nid in(");
		for (Integer nid : nids) {
			sb.append(nid + ",");
		}
		// 删除最后一个逗号
		sb.delete(sb.length() - 1, sb.length());
		sb.append(") ");
		PreparedStatement ps = this.conn.prepareStatement(sb.toString());
		return ps.executeUpdate() > 0;
	}

	@Override
	public Boolean doUpdate(News news) throws SQLException {

		String sql = "UPDATE news SET title=?, pubdate=?, content=?  WHERE nid=? ";
		PreparedStatement ps = this.conn.prepareStatement(sql);
		ps.setString(1, news.getTitle());
		ps.setDate(2, new java.sql.Date(news.getPubdate().getTime()));
		ps.setString(3, news.getContent());
		ps.setInt(4, news.getNid());
		return ps.executeUpdate() > 0;
	}

	@Override
	public List<News> doSplit(String column, String value, Integer curPage, Integer lineSize) throws SQLException {

		List<News> newsList = null;
		String sql = "SELECT nid,title,pubdate,content FROM news WHERE " + column + " LIKE ?  LIMIT ?,?";
		PreparedStatement ps = this.conn.prepareStatement(sql);
		ps.setString(1, "%" + value + "%");
		ps.setInt(2, (curPage - 1) * lineSize);
		ps.setInt(3, lineSize);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			newsList = new ArrayList<News>();
			rs.beforeFirst(); // 移动回来游标
			while (rs.next()) {
				News news = new News();
				news.setNid(rs.getInt(1));
				news.setTitle(rs.getString("title"));
				news.setPubdate(rs.getDate("pubdate"));
				news.setContent(rs.getString("content"));
				newsList.add(news);
			}
		}
		return newsList;
	}

	@Override
	public Integer getCount(String column, String value) throws SQLException {

		Integer count = 0;
		String sql = "SELECT count(1) FROM news  WHERE "+column + " LIKE ? ";
		PreparedStatement ps = this.conn.prepareStatement(sql);
		ps.setString(1, "%"+value+"%");
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			count = rs.getInt(1);
		}
		return count;
	}

	@Override
	public News doFindById(Integer nid) throws SQLException {

		String sql = "SELECT nid,title,pubdate,content FROM news WHERE nid=? ";
		PreparedStatement ps = this.conn.prepareStatement(sql);
		ps.setInt(1, nid);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			News news = new News();
			news.setNid(rs.getInt(1));
			news.setTitle(rs.getString("title"));
			news.setPubdate(rs.getDate("pubdate"));
			news.setContent(rs.getString("content"));
			return news;
		}
		return null;
	}

	@Override
	public List<News> doFind(String column, String value) throws SQLException {

		List<News> newsList = null;
		String sql = " SELECT nid,title,pubdate,content FROM news WHERE " + column + " LIKE ? ";
		PreparedStatement ps = this.conn.prepareStatement(sql);
		ps.setString(1, "%" + value + "%");
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			newsList = new ArrayList<News>();
			rs.beforeFirst(); // 移动回来游标
			while (rs.next()) {
				News news = new News();
				news.setNid(rs.getInt(1));
				news.setTitle(rs.getString("title"));
				news.setPubdate(rs.getDate("pubdate"));
				news.setContent(rs.getString("content"));
				newsList.add(news);
			}
		}
		return newsList;
	}

	/**
	 * 查询所有记录
	 */
	@Override
	public List<News> doFindAll() throws SQLException {
		return doFind("nid", "");
	}
	
	

}
